package cn.gson.crm.model.dao;

import java.sql.Date;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import cn.gson.crm.model.Jdbc;
import cn.gson.crm.model.entity.Student;
import cn.gson.crm.model.entity.StudentVo;
import cn.gson.crm.model.po.StudentPo;

public class StudentDao {
	private int pageSize=4;
	private AllotDao allotDao = new AllotDao();

	/**
	 * 查询所有已分班的学生
	 * @return
	 * @throws SQLException 
	 */
	public List<StudentVo> getGroupStudent(String name) throws SQLException{
		String sql="select a.student_name student_name,a.student_id student_id,c.clazz_name clazzname from student a inner join `group` b on a.student_id=b.group_student_id"
				+ " inner  join clazz c on b.group_clazz_id=c.clazz_id";
		List<Object> params = new ArrayList<>();
		if(name!=null&&!name.isEmpty()){
			sql+=" where a.student_name like ?";
			params.add("%"+name+"%");
		}
		List<Map<String,Object>> list=Jdbc.getInstace().executeQueryForList(sql,params.toArray());
		List<StudentVo> liststu=null;
		if(list!=null&&list.size()>0){
			liststu=new ArrayList<StudentVo>();
			for (Map<String,Object> map : list) {
				liststu.add(buids(map));
			}
		}
		return liststu;
	}

	/**
	 * 查询某咨询师手下的学生
	 * @return
	 * @throws SQLException 
	 */
	public List<StudentVo> getStudenByallot(String id) throws SQLException{
		String sql="select * from student a inner join allot b on a.student_id=b.allot_student_id"
				+ " where  b.allot_staff_id =? ";
		List<Map<String,Object>> list=Jdbc.getInstace().executeQueryForList(sql,id);
		List<StudentVo> liststu=null;
		if(list!=null&&list.size()>0){
			liststu=new ArrayList<StudentVo>();
			for (Map<String,Object> map : list) {
				liststu.add(buids(map));
			}
		}
		return liststu;
	}

	/**
	 * 查询某咨询师协助的学生
	 * @return
	 * @throws SQLException 
	 */
	public List<StudentVo> getStudenBycoopera(String id) throws SQLException{
		String sql="select * from student a inner join cooperation b on a.student_id=b.cooperation_student_id"
				+ " where  b.cooperation_staff_id =?  ";
		List<Map<String,Object>> list=Jdbc.getInstace().executeQueryForList(sql,id);
		List<StudentVo> liststu=null;
		if(list!=null&&list.size()>0){
			liststu=new ArrayList<StudentVo>();
			for (Map<String,Object> map : list) {
				liststu.add(buids(map));
			}
		}
		return liststu;
	}
	/**
	 * 查询所有学员
	 * 
	 * @param name
	 * @param pwd
	 * @return
	 * @throws SQLException
	 */
	public List<Student> getStudent() throws SQLException {
		String sql = "select * from student ";
		List<Map<String, Object>> data = Jdbc.getInstace().executeQueryForList(sql);
		List<Student> stu = null;
		if (data != null && data.size() > 0) {
			stu = new ArrayList<>();
			for (Map<String, Object> da : data) {
				stu.add(buid(da));
			}
		}
		return stu;
	}
	/**
	 * 查询所有学员
	 * 
	 * @param name
	 * @param pwd
	 * @return
	 * @throws SQLException
	 */
	public Student getStudentByname(String name) throws SQLException {
		String sql = "select * from student where student_name=? ";
		List<Map<String, Object>> data = Jdbc.getInstace().executeQueryForList(sql,name);
		Student stu = null;
		if (data != null && data.size() > 0) {
			stu = new Student();
			for (Map<String, Object> da : data) {
				stu=(buid(da));
			}
		}
		return stu;
	}
	/**
	 * 根据id查询学员的信息
	 * 孙超豪
	 * @throws SQLException
	 */
	public StudentPo getOneStudent(String studentId) throws SQLException {
		String sql = "select * from student where student_id = ?";
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql,studentId);
		StudentPo studentPo = null;
		if(map != null ){
			studentPo = new StudentPo();
			studentPo.setStudentId(Integer.parseInt(map.get("student_id").toString()));
			studentPo.setStudentAddress((String)map.get("student_address"));
			studentPo.setStudentName((String)map.get("student_name"));
			studentPo.setStudentParent((String)map.get("student_parent"));
			studentPo.setStudentParentPhone((String)map.get("student_parent_phone"));
			studentPo.setStudentPhone((String)map.get("student_phone"));
			studentPo.setStudentSex(Integer.parseInt(map.get("student_sex").toString()));
			studentPo.setStudentAllotId(Integer.parseInt(map.get("student_allot_id").toString()));
			String stna = "";
			if(map.get("student_allot_id")!=null){
				stna = allotDao.getStaffName(Integer.parseInt(map.get("student_allot_id").toString()));
			}
			studentPo.setStaffName(stna);
			studentPo.setStudentQq((String)map.get("student_qq"));
			String inna = "";
			if(map.get("student_interesting_id")!=null){
				inna = allotDao.getInteresting(Integer.parseInt(map.get("student_interesting_id").toString()));
			}
			studentPo.setInterestingName(inna);
			String s = map.get("student_comedate").toString().split(" ")[0];
			studentPo.setStudentComedate(java.sql.Date.valueOf(s));
			String sona = "";
			if(map.get("student_source_id")!=null){
				sona = allotDao.getSource(Integer.parseInt(map.get("student_source_id").toString()));
			}
			studentPo.setSourceName(sona);
			studentPo.setStudentRecode((String)map.get("student_recode"));
		}
		return studentPo;
	}
	
	/**
	 * 根据id查询学员的信息
	 * 孙超豪
	 * @throws SQLException
	 */
	public String getStudentName(String studentId) throws SQLException {
		String sql = "select student_name from student where student_id = ?";
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql,studentId);
		String name = "";
		if(map != null){
			name = (String)map.get("student_name");
		}
		return name;
	}
	/**
	 * 根据id查询学员的信息
	 * 孙超豪
	 * @throws SQLException
	 */
	public StudentPo getStudent(String studentId) throws SQLException {
		String sql = "select * from student where student_id = ?";
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql,studentId);
		StudentPo studentPo = null;
		if(map != null ){
			studentPo = new StudentPo();
			studentPo.setStudentId(Integer.parseInt(map.get("student_id").toString()));
			studentPo.setStudentAddress((String)map.get("student_address"));
			studentPo.setStudentName((String)map.get("student_name"));
			studentPo.setStudentParent((String)map.get("student_parent"));
			studentPo.setStudentParentPhone((String)map.get("student_parent_phone"));
			studentPo.setStudentPhone((String)map.get("student_phone"));
			studentPo.setStudentSex(Integer.parseInt(map.get("student_sex").toString()));
			studentPo.setStaffName(String.valueOf(map.get("student_allot_id")));
			studentPo.setStudentQq((String)map.get("student_qq"));
			studentPo.setInterestingName( String.valueOf(map.get("student_interesting_id")));
			String s = map.get("student_comedate").toString().split(" ")[0];
			studentPo.setStudentComedate(java.sql.Date.valueOf(s));
			studentPo.setSourceName( String.valueOf(map.get("student_source_id")));
			studentPo.setStudentRecode((String)map.get("student_recode"));
		}
		return studentPo;
	}
	/**
	 * 条件查询所有学员
	 * 
	 * @param name
	 * @param pwd
	 * @return
	 * @throws SQLException
	 */
	public List<Map<String, Object>> getStudent(String source, String interesting, String name
			, String isallot,String staffname) throws SQLException {
		StringBuilder sql = new StringBuilder();
		sql.append("select a.student_name student_name,a.student_id student_id,a.student_address student_address,"
				+ " a.student_comedate student_comedate,a.student_parent student_parent,"
				+ " a.student_parent_phone student_parent_phone,a.student_phone student_phone,"
				+ " a.student_qq student_qq,a.student_recode student_recode,"
				+ " a.student_sex student_sex ,a.student_source_id student_source_id ,b.source_name source_name,"
				+ " c.interesting_name interesting_name,a.student_allot_id student_allot_id,e.staff_name staffname,"
				+ " g.clazz_name clazzname,t.total_student_id total_student_id "
				+ "  from student a inner join source b "
				+ " on a.student_source_id=b.source_id "
				+ " inner join interesting c on "
				+ " a.student_interesting_id=c.interesting_id "
				+ " left join allot d on a.student_id=d.allot_student_id "
				+ " left join staff e on d.allot_staff_id=e.staff_id  LEFT join `group` f on a.student_id"
				+ " =f.group_student_id LEFT JOIN clazz g on f.group_clazz_id=g.clazz_id left join total t "
				+ " on a.student_id=t.total_student_id  where 1=1 ");
		List<Object> params = new ArrayList<>();
		buildSql(params, sql, source, interesting, name,isallot,staffname);
		List<Map<String, Object>> data = Jdbc.getInstace().executeQueryForList(sql.toString(), params.toArray());
		return data;
	}

	/**
	 * 条件查询学员分页
	 * 
	 * @param name
	 * @param pwd
	 * @return
	 * @throws SQLException
	 */
	public List<StudentVo>  getStudentBy(int page,String source, String interesting, String name
			, String isallot,String staffname) throws SQLException {
		StringBuilder sql = new StringBuilder();
		sql.append("select a.student_name student_name,a.student_id student_id,a.student_address student_address,"
				+ " a.student_comedate student_comedate,a.student_parent student_parent,"
				+ " a.student_parent_phone student_parent_phone,a.student_phone student_phone,"
				+ " a.student_qq student_qq,a.student_recode student_recode,"
				+ " a.student_sex student_sex ,a.student_source_id student_source_id ,b.source_name source_name,"
				+ " c.interesting_name interesting_name,a.student_allot_id student_allot_id,e.staff_name staffname,"
				+ "g.clazz_name clazzname,t.total_student_id total_student_id "
				+ "  from student a inner join source b "
				+ " on a.student_source_id=b.source_id "
				+ " inner join interesting c on "
				+ " a.student_interesting_id=c.interesting_id "
				+ " left join allot d on a.student_id=d.allot_student_id "
				+ " left join staff e on d.allot_staff_id=e.staff_id  LEFT join `group` f on a.student_id"
				+ " =f.group_student_id LEFT JOIN clazz g on f.group_clazz_id=g.clazz_id left join total t "
				+ " on a.student_id=t.total_student_id  where 1=1 ");
		List<Object> params = new ArrayList<>();
		buildSql(params, sql, source, interesting, name,isallot,staffname);
		sql.append(" order by t.total_student_id ,a.student_allot_id, a.student_id desc limit ?,? ");
		int size = (page - 1) * pageSize;
		params.add(size);
		params.add(pageSize);
		List<Map<String, Object>> listData = Jdbc.getInstace().executeQueryForList(sql.toString(), params.toArray());
		List<StudentVo> stu = null;
		if (listData != null && listData.size() > 0) {
			stu = new ArrayList<>();
			for (Map<String, Object> data : listData) {
				stu.add(buids(data));
			}
		}
		return stu;
	}


	/**
	 * 条件查询总页数
	 * 
	 * @return
	 * @throws SQLException
	 */
	public int getTotal(String source, String interesting, String name
			, String isallot,String staffname) throws SQLException {
		StringBuilder sql = new StringBuilder();
		sql.append("select count(*) total  from student a inner join source b "
				+ " on a.student_source_id=b.source_id "
				+ " inner join interesting c on "
				+ " a.student_interesting_id=c.interesting_id "
				+ " left join allot d on a.student_id=d.allot_student_id "
				+ " left join staff e on d.allot_staff_id=e.staff_id  LEFT join `group` f on a.student_id"
				+ " =f.group_student_id LEFT JOIN clazz g on f.group_clazz_id=g.clazz_id left join total t "
				+ " on a.student_id=t.total_student_id  where 1=1 ");
		List<Object> params = new ArrayList<>();
		buildSql(params, sql, source, interesting, name,isallot,staffname);
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql.toString(), params.toArray());
		Integer total = Integer.parseInt(map.get("total").toString());
		return (int) Math.ceil((double) total / pageSize);
	}

	/**
	 * 总页数
	 * 
	 * @return
	 * @throws SQLException
	 */
	public int getTotal() throws SQLException {
		String sql = "select count(*) total from staff";
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql);
		Integer total = Integer.parseInt(map.get("total").toString());
		return (int) Math.ceil((double) total / pageSize);
	}

	/**
	 * 新增学员
	 * 
	 * @param s
	 * @return
	 * @throws SQLException
	 */
	public boolean insertStudent(Student s) throws SQLException {
		String sql = "insert into Student(student_name,student_sex,student_qq,student_phone,student_address,student_parent,student_parent_phone,student_recode,student_interesting_id,student_source_id) value(?,?,?,?,?,?,?,?,?,?)";
		return Jdbc.getInstace().executeUpdate(sql, s.getStudentName(), s.getStudentSex(),s.getStudentQq(),s.getStudentPhone(),s.getStudentAddress(),
				s.getStudentParent(),s.getStudentParentPhone(),s.getStudentRecode(),s.getStudentInterestingId(),s.getStudentSourceId());
	}

	/**
	 *分配学员给咨询师
	 * 
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public boolean allotStaff(String stuid ,String staffid ) throws SQLException {
		String sql = "update student set student_allot_id = ? where student_id=?";
		return Jdbc.getInstace().executeUpdate(sql,staffid, stuid);
	}

	/**
	 * 修改学员信息
	 * 
	 * @param
	 * @return
	 * @throws SQLException
	 */
	public boolean updateStudentById(Student s) throws SQLException {
		String sql = "update student set student_name=?,student_sex=?,student_qq=?,student_phone=?,student_address=?,student_parent=?,student_parent_phone=?,student_recode=?,student_interesting_id=?,student_source_id=? where  student_id=?";
		return Jdbc.getInstace().executeUpdate(sql, s.getStudentName(), s.getStudentSex(),s.getStudentQq(),s.getStudentPhone(),s.getStudentAddress(),
				s.getStudentParent(),s.getStudentParentPhone(),s.getStudentRecode(),s.getStudentInterestingId(),s.getStudentSourceId(),s.getStudentId());
	}

	/**
	 * 绑定参数
	 * 
	 * @param data
	 * @return
	 */
	private Student buid(Map<String, Object> data) {
		Student s = new Student();
	
		if (data.get("student_address") != null) {
			s.setStudentAddress(data.get("student_address").toString());
		}
		if(data.get("student_phone")!=null){
			s.setStudentPhone(data.get("student_phone").toString());
		}
		if (data.get("student_allot_id") != null) {
			s.setStudentAllotId(Integer.parseInt(data.get("student_allot_id").toString()));
		}
		if (data.get("student_comedate") != null) {
			SimpleDateFormat sf=new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss");
			try {
				java.util.Date  d=sf.parse(data.get("student_comedate").toString());
				Date  date=new Date(d.getTime());
				s.setStudentComedate(date);
			} catch (ParseException e) {
				e.printStackTrace();
			}
			
		}
		if (data.get("student_id") != null) {
			s.setStudentId(Integer.parseInt(data.get("student_id").toString()));
		}
		if (data.get("student_interesting_id") != null) {
			s.setStudentInterestingId(Integer.parseInt(data.get("student_interesting_id").toString()));
		}
		if (data.get("student_name") != null) {
			s.setStudentName(data.get("student_name").toString());
		}
		if (data.get("student_parent") != null) {
			s.setStudentParent(data.get("student_parent").toString());
		}
		if (data.get("student_parent_phone") != null) {
			s.setStudentParentPhone(data.get("student_parent_phone").toString());
		}
		if (data.get("student_qq") != null) {
			s.setStudentQq(data.get("student_qq").toString());
		}
		if (data.get("student_recode") != null) {
			s.setStudentRecode(data.get("student_recode").toString());
		}
		if (data.get("student_sex") != null) {
			s.setStudentSex(Integer.parseInt(data.get("student_sex").toString()));
		}
		if (data.get("student_source_id") != null) {
			s.setStudentSourceId(Integer.parseInt(data.get("student_source_id").toString()));
		}
		return s;
	}

	/**
	 * 绑定STudentVo参数
	 * 
	 * @param data
	 * @return
	 */
	private StudentVo buids(Map<String, Object> data) {
		StudentVo s = new StudentVo();
		if (data.get("interesting_name") != null) {
			s.setInterestingName(data.get("interesting_name").toString());
		}
		if (data.get("source_name") != null) {
			s.setSourceName( data.get("source_name").toString());
		}
		if (data.get("student_address") != null) {
			s.setStudentAddress(data.get("student_address").toString());
		}
		if(data.get("student_phone")!=null){
			s.setStudentPhone(data.get("student_phone").toString());
		}
		if (data.get("student_allot_id") != null) {
			s.setStudentAllotId(Integer.parseInt(data.get("student_allot_id").toString()));
		}
		if (data.get("total_student_id") != null) {
			s.setTotal_student_id(Integer.parseInt(data.get("total_student_id").toString()));
		}
		if (data.get("student_comedate") != null) {
			SimpleDateFormat sf=new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss");
			try {
				java.util.Date  d=sf.parse(data.get("student_comedate").toString());
				Date  date=new Date(d.getTime());
				s.setStudentComedate(date);
			} catch (ParseException e) {
				e.printStackTrace();
			}
			
		}
		if (data.get("student_id") != null) {
			s.setStudentId(Integer.parseInt(data.get("student_id").toString()));
		}
		if (data.get("student_interesting_id") != null) {
			s.setStudentInterestingId(Integer.parseInt(data.get("student_interesting_id").toString()));
		}
		if (data.get("student_name") != null) {
			s.setStudentName(data.get("student_name").toString());
		}
		if (data.get("student_parent") != null) {
			s.setStudentParent(data.get("student_parent").toString());
		}
		if (data.get("student_parent_phone") != null) {
			s.setStudentParentPhone(data.get("student_parent_phone").toString());
		}
		if (data.get("student_qq") != null) {
			s.setStudentQq(data.get("student_qq").toString());
		}
		if (data.get("student_recode") != null) {
			s.setStudentRecode(data.get("student_recode").toString());
		}
		if (data.get("student_sex") != null) {
			s.setStudentSex(Integer.parseInt(data.get("student_sex").toString()));
		}
		if (data.get("student_source_id") != null) {
			s.setStudentSourceId(Integer.parseInt(data.get("student_source_id").toString()));
		}
		if (data.get("staffname") != null) {
			s.setStaffName(data.get("staffname").toString());
		}
		if (data.get("clazzname") != null) {
			s.setClazzname(data.get("clazzname").toString());
		}
		return s;
	}

	/**
	 * 条件绑定
	 * 
	 * @param params
	 * @param sql
	 * @param type
	 * @param userName
	 * @param dept
	 * @param tel
	 * @return
	 */
	private StringBuilder buildSql(List<Object> params, StringBuilder sql, String source, String interesting, String name
			, String isallot,String staffname) {
		if (source != null && !source.isEmpty()) {
			sql.append(" and student_source_id=?");
			params.add(source);
		}
		if (interesting != null && !interesting.isEmpty()) {
			sql.append(" and student_interesting_id = ? ");
			params.add( interesting);
		}
		if (name != null && !name.isEmpty()) {
			sql.append(" and student_name like ? ");
			params.add("%" + name + "%");
		}
		if (isallot != null && !isallot.isEmpty()) {
			if("0".equals(isallot)){
				sql.append(" and student_allot_id = ?");
			}
			else{
				sql.append(" and student_allot_id <> ?");
				isallot = "0";
			}
			params.add(isallot);
		}
		if (staffname != null && !staffname.isEmpty()) {
			sql.append(" and  d.allot_staff_id= ? ");
			params.add(staffname);
		}
		return sql;
	}

}
